#!/usr/bin/env python
#-*- coding:utf-8 -*-
import os,sys
from wwpy.db.DBI import DBI,dbc

class DataDict(object):
    pass

class DBDict(DataDict):
    
    def __init__(self,db=None,table=None,coding='utf8',id=None,name=None):
        self.db=db
        self.table=table
        self.coding=coding
        self.id=id
        self.name=name
        self.dbi=DBI(coding=self.coding,**dbc[self.db])
        self._fields=None
        
    def fields(self):
        sql="SHOW FULL FIELDS FROM %s " % self.table
        rows=self.dbi.fetch(sql)
        return map(lambda row:row[0],rows)
        
    def check_fields(self,fields):
        default='*'
        if fields is None:
            return default
        if self._fields is None:
            self._fields=self.fields()
        for field in fields:
            assert field in self._fields,'No field %s in %s' % (field,tuple(self._fields))
        _fields=default
        if isinstance(fields,(list,tuple)):
            _fields=','.join(str(field) for field in fields)
        return _fields
    
    def set_coding(self,coding='utf8'):
        self.dbi.set_dbc(coding='utf8')
    
    def add_conditions(self,conditions):
        return self.dbi.add_conditions(conditions)
    
    def select(self,fields=None,conditions=None):
        fields=self.check_fields(fields)
        sql="SELECT %s FROM %s WHERE 1=1 " % (fields,self.table)
        conditions=self.add_conditions(conditions)
        if conditions:
            sql+='AND %s ' % conditions
        return self.dbi.fetch(sql)
    
    def select_by_id(self,id,fields=None,conditions=None):
        fields=self.check_fields(fields)
        sql="SELECT %s FROM %s WHERE %s=%s " % (fields,self.table,self.id,id)
        conditions=self.add_conditions(conditions)
        if conditions:
            sql+='AND %s ' % conditions
        return self.dbi.fetch(sql)
        
    def select_by_name(self,name,fields=None,conditions=None):
        fields=self.check_fields(fields)
        sql="SELECT %s FROM %s WHERE %s='%s' " % (fields,self.table,self.name,name)
        conditions=self.add_conditions(conditions)
        if conditions:
            sql+='AND %s ' % conditions
        return self.dbi.fetch(sql)
    
    def rebuild(self,lang='perl',script=None,*args):
        args=' '.join(str(arg) for arg in args) if args else ''
        cmd="%s %s %s &" %(lang,script,args)
        os.system(cmd)
        
    def scan(self,rows,sep='\t'):
        for row in rows:
            print sep.join(str(field) for field in row)
    
    def store(self,results,file,sep='\t'):
        f=open(file,'w')
        for result in results:
            f.write(sep.join(str(field) for field in result)+'\n')
        f.close()
        
class Readme(object):
    
    def __init__(self):
        self.name=self._name()
        self.author=self._author()
        self.language=self._language()
        self.version=self._version()
        self.usage=self._usage()
        
    def _author(self):
        return """
        WangWei - Major programmer.
        """
    
    def _version(self):
        return 1.0
    
    def _language(self):
        return """
        Python 2.7 and above versions. 
        """
        
    def _name(self):
        return 'DataDict'
    
    def _usage(self):
        return """
    import os,sys
    from wwpy.wuba.data.DataDict import DisplayCateDict,BelongCateDict,DisplayLocalDict,BelongLocalDict,
                                        CmcsDict,MerDict,ProductLineDict,ProductUnitDict,LargeAreaDict
    
    #DisplayCateDict为例:
    
    obj=DisplayCateDict()
    rows=obj.select_by_id(7899,fields=('cate_id','cate_name','cate1','cate2','cate3')) #按ID查询
    #其中rows为生成器,保留每行的记录;fields为字段名称,默认为所有字段即*;conditions为条件,可选.
    for row in rows:
        print ','.join(str(field) for field in row)
        
    rows=obj.select_by_name('短租房/日租房',fields=('cate_id','cate_name','cate1','cate2','cate3')) #按名称查询
    obj.scan(rows) #浏览记录
    
    rows=obj.select_by_channel('二手频道',fields=('channel_name','cate1','cate2','cate3'))
    #按频道查询,频道可以是ID,也可以是频道名称.
    obj.scan(rows)
    
    rows=obj.select(fields=['cate_id','cate_name'],conditions={'channel_id':1000002})
    #选择所有记录,fields和conditions均可选,上面的select_by_*方法中都可以使用.
    obj.scan(rows)
    
    fields=obj.fields() #查询表中所有字段名称,fields为list.
    
    obj.store(rows,file,sep='\t') #将结果集存储到文件中.
    
    obj.rebuild() #重建字典表,有的类没有实现这个功能,可以使用父类的 obj.rebuild(lang='perl',script='script.pl',*args)
        """
        
    def readme(self):
        print
        print "DataDict's Readme"
        print
        print "=" * 60
        for k,v in self.__dict__.items():
            print k.upper()+": "+str(v)
            print "=" * 60
    
    
class DisplayCateDict(DBDict):
    
    def __init__(self,db='db_base_dict',table='display_cate',coding='utf8',id='cate_id',name='cate_name'):
        super(DisplayCateDict,self).__init__(db,table,coding,id,name)
        
    def select_by_listname(self,list_name,fields=None,conditions=None):
        name=self.name
        self.name='list_name'
        rows=self.select_by_name(list_name,fields,conditions)
        self.name=name
        return rows
    
    def select_by_channel(self,channel,fields=None,conditions=None):
        channel_id,channel_name=None,None
        try:
            channel_id=int(channel)
        except ValueError:
            channel_name=channel
        conditions=conditions or {}
        if channel_id is not None:
            conditions['channel_id']=channel_id
        if channel_name is not None:
            conditions['channel_name']=channel_name
        return self.select(fields=fields,conditions=conditions)
    
    def rebulid(self):
        super(DisplayCateDict,self).rebuild('perl','/work/opt/analytic/perl/EC58/All58/Common/LocalCate.pm')
        
    @classmethod
    def demo(cls,has_listname=True):
        obj=cls()
        rows=obj.select_by_id(7899,fields=('cate_id','cate_name','cate1','cate2','cate3'))
        for row in rows:
            print ','.join(str(field) for field in row)
        rows=obj.select_by_name('短租房/日租房',fields=('cate_id','cate_name','cate1','cate2','cate3'))
        obj.scan(rows)
        rows=obj.select(fields=['cate_id','cate_name'],conditions={'channel_id':1000002})
        obj.scan(rows)
        rows=obj.select_by_channel('二手频道',fields=('channel_name','cate1','cate2','cate3'))
        obj.scan(rows)
        if has_listname:
            rows=obj.select_by_listname('zufang',fields=('list_name','cate1','cate2','cate3'))
            obj.scan(rows)
        
class DisplayLocalDict(DBDict):
    
    def __init__(self,db='db_base_dict',table='display_local',coding='utf8',id='local_id',name='local_name'):
        super(DisplayLocalDict,self).__init__(db,table,coding,id,name)
        
    def select_by_listname(self,list_name,fields=None,conditions=None):
        name=self.name
        self.name='list_name'
        row=self.select_by_name(list_name,fields,conditions)
        self.name=name
        return row
    
    def rebulid(self):
        super(DisplayLocalDict,self).rebuild('perl','/work/opt/analytic/perl/EC58/All58/Common/LocalCate.pm')
        
    @classmethod
    def demo(cls,has_listname=True):
        obj=cls()
        rows=obj.select_by_id(10533,fields=('local_id','local_name','city1','city2','city3'))
        for row in rows:
            print ','.join(str(field) for field in row)
        obj.set_coding('gbk')
        rows=obj.select_by_name('鲁山',fields=('local_id','local_name','city1','city2','city3'))
        obj.scan(rows)
        obj.set_coding('utf8')
        rows=obj.select(fields=['local_id','local_name'],conditions={'city1':'北京'})
        obj.scan(rows)
        if has_listname:
            rows=obj.select_by_listname('changanjiedao',fields=('list_name','city1','city2','city3'))
            obj.scan(rows)
        
class BelongCateDict(DisplayCateDict):
    
    def __init__(self,table='belong_cate',**kwargs):
        super(BelongCateDict,self).__init__(table=table,**kwargs)
        
class BelongLocalDict(DisplayLocalDict):
    
    def __init__(self,table='belong_local',**kwargs):
        super(BelongLocalDict,self).__init__(table=table,**kwargs)
        
class CmcsDict(DBDict):
    
    def __init__(self,db='db_base_dict',table='t_cmcs_param',coding='utf8',id='param_id',name='param_name'):
        super(CmcsDict,self).__init__(db,table,coding,id,name)
        
    def select_by_listname(self,list_name,fields=None,conditions=None):
        name=self.name
        self.name='list_name'
        rows=self.select_by_name(list_name,fields,conditions)
        self.name=name
        return rows
    
    @classmethod
    def demo(cls):
        obj=cls()
        rows=obj.select_by_id(7573)
        obj.scan(rows)
        rows=obj.select_by_name('RoomCount')
        obj.scan(rows)
        rows=obj.select(fields=['param_id','param_name'],conditions={'param_type':0})
        obj.scan(rows)
        
class MerDict(DBDict):
    
    def __init__(self,db='db_base_dict',table='pmc_mer',coding='utf8',id='mer_id',name='mer_name'):
        super(MerDict,self).__init__(db,table,coding,id,name)
    
    @classmethod
    def demo(cls):
        obj=cls()
        obj.dbi.coding='gbk'
        rows=obj.select_by_id(1050)
        obj.scan(rows)
        obj.dbi.coding='utf8'
        rows=obj.select_by_name('同城交友')
        obj.scan(rows)
            
class ProductLineDict(DBDict):
    
    def __init__(self,db='db_base_dict',table='t_product_line',coding='utf8',id='id',name='name'):
        super(ProductLineDict,self).__init__(db,table,coding,id,name)
    
    @classmethod
    def demo(cls):
        obj=cls()
        obj.dbi.coding='gbk'
        rows=obj.select_by_id(1)
        obj.scan(rows)
        obj.dbi.coding='utf8'
        rows=obj.select_by_name('会员招聘')
        obj.scan(rows)
        
class ProductUnitDict(DBDict):
    
    def __init__(self,db='db_base_dict',table='t_product_unit',coding='utf8',id='id',name='name'):
        super(ProductUnitDict,self).__init__(db,table,coding,id,name)
    
    @classmethod
    def demo(cls):
        obj=cls()
        obj.dbi.coding='gbk'
        rows=obj.select_by_id(1)
        obj.scan(rows)
        obj.dbi.coding='utf8'
        rows=obj.select_by_name('生活网邻通')
        obj.scan(rows)
            
class LargeAreaDict(DBDict):
    
    def __init__(self,db='db_base_dict',table='large_area_city',coding='utf8',id='area_id',name='area_name'):
        super(LargeAreaDict,self).__init__(db,table,coding,id,name)
        
    def select_by_city(self,city,fields=None,conditions=None):
        city_id,city_name=None,None
        try:
            city_id=int(city)
        except ValueError:
            city_name=city
        conditions=conditions or {}
        if city_id is not None:
            conditions['city_id']=city_id
        if city_name is not None:
            conditions['city_name']=city_name
        return self.select(fields=fields,conditions=conditions)
    
    @classmethod
    def demo(cls):
        obj=cls()
        rows=obj.select_by_id(1)
        obj.scan(rows)
        obj.set_coding('utf8')
        rows=obj.select_by_name('川渝冀大区')
        obj.scan(rows)
        rows=obj.select_by_city('石家庄')
        obj.scan(rows)
        
if __name__ == '__main__':
    #DisplayCateDict.demo()
    #BelongCateDict.demo(has_listname=False)
    #DisplayLocalDict.demo()
    #BelongLocalDict.demo(has_listname=False)
    #CmcsDict().demo()
    #MerDict().demo()
    #ProductLineDict().demo()
    #ProductUnitDict.demo()
    LargeAreaDict.demo()
    #Readme().readme()
    
    
